﻿/*
TO BE RUN DIRECTLY ON THE LOCALDB DATABASE VIA A QUERY WINDOW TO ADD
AN NVARCHAR(50) COLUMN NAMED ExternalID TO EVERY NON HEAP TABLE IN THE 
DATABASE

DATE: MARCH 2013

WRITTEN BY JAMES SKIPWITH, http://sqlpimp.com/

YOU MAY ALTER THIS CODE AS YOU WISH. KNOCK YOURSELF OUT BUT...THIS CODE AND 
INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED 
OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY 
AND/OR FITNESS FOR A PARTICULAR PURPOSE.
*/

/*
SELECT * FROM sys.columns WHERE [name] =  N'ExternalID'
*/

--EXECUTE AS USER = 'todocuser';

DECLARE @COLUMN_ADD_SQL NVARCHAR(MAX) = '';

SELECT	@COLUMN_ADD_SQL += 'IF NOT EXISTS (SELECT * FROM sys.columns WHERE [object_id] = '
		+ CONVERT(VARCHAR(100), [object_id]) + ' AND [name] = N''ExternalID'') '
		+ 'ALTER TABLE ' + QUOTENAME(TableSchema) + '.' + 
		QUOTENAME(TableName) + ' ADD ExternalID NVARCHAR(50) CONSTRAINT [DF_' + TableName + '_ExternalID] DEFAULT('''') NOT '
		+ 'NULL;' + CHAR(13) + CHAR(13)
FROM [todoc].[svNonHeapTables];

--SELECT @COLUMN_ADD_SQL;

EXEC sp_executesql @COLUMN_ADD_SQL;

--REVERT;
